package com.tool4j.util;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import com.tool4j.entity.TableMetadata;
import com.tool4j.entity.vo.SqlTable;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.statement.Statement;

import java.io.StringReader;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author Deng.Weiping
 * @since 2023/11/22 14:38
 */
@Slf4j
public class SqlParserUtil {

    public static List<TableMetadata> parser(String input) throws JSQLParserException {
        List<TableMetadata> result = new ArrayList<>();
        String regex = "(?i)create\\s+table.*?(?=create\\s+table|$)";
        Pattern pattern = Pattern.compile(regex, Pattern.DOTALL);
        Matcher matcher = pattern.matcher(input);
        // 遍历匹配结果
        while (matcher.find()) {
            String item = matcher.group(0);
            if (StrUtil.isBlank(item)) {
                continue;
            }
            item = replaceCommentSemicolon(item);
            if (item.contains(";")) {
                for (String s : item.split(";")) {
                    if (StrUtil.isBlank(item)) {
                        continue;
                    }
                    TableMetadata parserItem = parserItem(s);
                    if (parserItem != null) {
                        result.add(parserItem);
                    }
                }
            } else {
                TableMetadata parserItem = parserItem(item);
                if (parserItem != null) {
                    result.add(parserItem);
                }
            }
        }
        return result;
    }

    private static TableMetadata parserItem(String sql) {
        try {
            String crtRegex = "(?i)create\\s+table\\s+(.*?)\\)(.*?)";
            Pattern crtPattern = Pattern.compile(crtRegex, Pattern.DOTALL);
            Matcher crtMatcher = crtPattern.matcher(sql);
            if (!crtMatcher.matches()) {
                return null;
            }
            String characterRegex = "(?i)CHARACTER\\s+SET\\s*=\\s*\\w+";
            String characterRegex2 = "(?i)CHARACTER\\s+SET\\s+\\w+\\s+";
            sql = sql.replaceAll(characterRegex, " ").replaceAll(characterRegex2, " ");
            CCJSqlParserManager mgr = new CCJSqlParserManager();
            Statement stmt = mgr.parse(new StringReader(sql));
            JSqlVisitor sqlVisitor = JSqlVisitor.newInstance();
            stmt.accept(sqlVisitor);
            return sqlVisitor.getTableMetadata(sql);
        } catch (Exception e) {
            return null;
        }
    }

    /**
     * 替换注释中的分号，避免解析误判
     *
     * @param sql
     * @return
     */
    public static String replaceCommentSemicolon(String sql) {
        // 正则表达式匹配单引号或双引号之间的内容
        String singleQuotePattern = "('([^']|'')*')";
        String doubleQuotePattern = "\"[^\"]*\"";
        String pattern = String.format("%s|%s", singleQuotePattern, doubleQuotePattern);

        Pattern regex = Pattern.compile(pattern);
        Matcher matcher = regex.matcher(sql);

        String result = sql;
        // 查找匹配的内容并判断分号位置
        while (matcher.find()) {
            String match = matcher.group();
            // 判断分号位置
            if (match.contains(";")) {
                result = result.replace(match, match.replace(";", "；"));
            }
        }
        return result;
    }

    /**
     * 解析sql语句
     *
     * @param sql
     * @return {@link SqlTable}
     */
    public static SqlTable parserSqlTable(String sql) {
        try {
            sql = sql.replace("`", "");
            CCJSqlParserManager mgr = new CCJSqlParserManager();
            Statement stmt = mgr.parse(new StringReader(sql));
            JSqlVisitor sqlVisitor = JSqlVisitor.newInstance();
            stmt.accept(sqlVisitor);
            return sqlVisitor.getSqlTable();
        } catch (Exception e) {
            log.error("解析sql失败");
        }
        return null;
    }

    public static String parser2JavaType(String dbType) {
        if (dbType == null) {
            return "String";
        }
        String typeUpperCase = dbType.toUpperCase();
        return switch (typeUpperCase) {
            case "INT", "INT4", "INT8", "INT16", "TINYINT", "SMALLINT", "MEDIUMINT", "INTEGER" -> "Integer";
            case "BIGINT" -> "Long";
            case "FLOAT", "DOUBLE", "DECIMAL", "NUMBER" -> "Float";
            case "BOOLEAN" -> "Boolean";
            case "ENUM", "SET" -> "List";
            case "DATE", "DATETIME", "TIMESTAMP" -> "Date";
            default -> "String";
        };
    }

    public static String getColumnComment(List<String> columnSpecs) {
        if (CollUtil.isNotEmpty(columnSpecs)) {
            for (int i = 0; i < columnSpecs.size(); i++) {
                if (JSqlVisitor.COMMENT.equalsIgnoreCase(columnSpecs.get(i)) && i < columnSpecs.size() - 1) {
                    return columnSpecs.get(i + 1).replaceAll("'", "");
                }
            }
        }
        return " ";
    }
}
